
from django.http import HttpResponseRedirect
from django.shortcuts import render_to_response
from django.template import RequestContext
from django.core.urlresolvers import reverse

from achievements.models import Achievement, Fulfilment
from accounts.models import UserProfile

SELECT_TOPALL_QUERY = 'SELECT achievements_achievement.id AS id, COUNT(*) AS count FROM achievements_achievement INNER JOIN achievements_fulfilment ON achievements_achievement.id = achievements_fulfilment.achievement_id WHERE achievements_achievement.author_id IS NULL GROUP BY achievements_achievement.id ORDER BY count DESC'

SELECT_TOPUSERACH_QUERY = 'SELECT achievements_achievement.id, COUNT(*) AS count FROM achievements_achievement INNER JOIN achievements_fulfilment ON achievements_achievement.id=achievements_fulfilment.achievement_id WHERE achievements_achievement.author_id IS NOT NULL GROUP BY achievements_achievement.id ORDER BY count DESC'

SELECT_TOPUSERS_QUERY = 'SELECT accounts_userprofile.id, accounts_userprofile.user_points, COUNT(*) AS countdone FROM accounts_userprofile INNER JOIN achievements_fulfilment ON accounts_userprofile.id=achievements_fulfilment.user_id GROUP BY accounts_userprofile.id ORDER BY accounts_userprofile.user_points DESC'

SELECT_ACHI_MADE_BY_USER = 'SELECT achievements_achievement.id, COUNT(*) AS countmade FROM achievements_achievement WHERE achievements_achievement.author_id = %s'

def main_page(request):
    #top_achievements_list = Achievement.objects.all()
    top_achievements_list = Achievement.objects.raw(SELECT_TOPALL_QUERY)[:10]
    top_achievements_list = [x for x in top_achievements_list if x.count > 0]
    
    top_achievements_list_user = Achievement.objects.raw(SELECT_TOPUSERACH_QUERY)[:10]
    top_achievements_list_user =  [x for x in top_achievements_list_user if x.count > 0]
    
    top_users_list = UserProfile.objects.raw(SELECT_TOPUSERS_QUERY)[:10]
    top_users_list = [x for x in top_users_list if x.countdone > 0]
    
    top_users_list = dict.fromkeys(top_users_list, 0)
    for user in top_users_list.iterkeys():
	tmplist = Achievement.objects.raw(SELECT_ACHI_MADE_BY_USER, [user.id])[0]
	if (tmplist is not None):
	  top_users_list[user] = tmplist.countmade

    return render_to_response('index.html',
        {'top_achievements_list': top_achievements_list, 'top_achievements_list_user' : top_achievements_list_user, 'top_users_list': top_users_list},
        context_instance=RequestContext(request))

